/**
 * 
 */
package monster.comm.util;

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * 
* 创建信息=============
* 类名: monster.comm.util.ErrVo
* 类作用描述: 解析excel
* 创建人： wanghong 
* 创建时间： 2013-1-7 下午04:58:52
* 修改信息1=============
* 修改类型:     //1.修改bug 2.业务变化修改
* 修改描述: 
* 修改人：  
* 修改时间：
 */
public class Excel {
	private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	public static void main(String args[]) throws Exception {
//		Object obj[][] = null;
//		try {
//			HSSFWorkbook excel = Excel.openexcel("d:/poi/q1.xls");// 打开excel文件
//			List<HSSFSheet> list = Excel.getsheet(excel);// 获取打开工作薄中表集合
//			for (HSSFSheet ss : list) {
//				obj = Excel.getsheetValue(ss);
//				if (obj != null) {
//					for (int i = 0; i < obj.length; i++) {
//						if (obj[i].length > 0) {
//							for (int j = 0; j < obj[i].length; j++) {
//								int b = obj[i][j].toString().getBytes("GB2312").length;
//								String ss1 = "~{" + b + "}";
//								System.out
//										.print(new String("~~~~~~~~~~")
//												.replaceFirst(ss1,
//														obj[i][j].toString()));
//							}
//						}
//						System.out.println("\n");
//					}
//				}
//			}
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
		Set<Object[][]> set = Excel.getResult("d:/poi/q1.xls");
		for(Iterator<Object[][]> it=set.iterator();it.hasNext();){
			Object [][] s = it.next();
			for(int i=0;i<s.length;i++){
				for(int j=0;j<s[i].length;j++){
					System.out.print(s[i][j]+"   ");
				}
				System.out.println(); 
			}
		}
		
	}

	public static Set<Object[][]> getResult(String excelPath) throws Exception{
		Set<Object[][]> set = new HashSet<Object[][]>();
		try {
			Workbook excel = Excel.openexcel(excelPath);// 打开excel文件
			List<Sheet> list = Excel.getsheet(excel);// 获取打开工作薄中表集合
			for (Sheet ss : list) {// 工作波
				Object [][] obj = Excel.getsheetValue(ss);
				
				
				if (obj != null) {
					set.add(obj);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e);
		}
		return set;
	}

	private static Workbook openexcel(String filenam) throws Exception {
		Workbook excel=null;
		File ff = new File(filenam);
		  if(ff.exists()){
			  if(filenam.endsWith(".xlsx"))
				excel=new XSSFWorkbook(new FileInputStream(new File(filenam)));
			  else if(filenam.endsWith(".xls"))
				excel=new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(ff)));
			  else System.out.println("你传入的不是Excel文件,请导入Excle文件!");
		  }else{
			  System.out.println("文件不存在!");
		  }
		return excel;
	}

	private static List<Sheet> getsheet(Workbook excel) throws Exception {
		List<Sheet> list = new ArrayList<Sheet>();
		int sheetnum = excel.getNumberOfSheets();
		for (int i = 0; i < sheetnum; i++) {
			list.add(excel.getSheetAt(i));
		}
		return list;
	}

	private static Object[][] getsheetValue(Sheet sheet) throws Exception {
		int rows = sheet.getPhysicalNumberOfRows();
		Object[][] obj = new Object[rows][];
		;
		if (rows > 0) {
			sheet.getMargin(Sheet.TopMargin);// 移动指针到文件顶部
			for (int j = 0; j < rows; j++) {
				Row row = sheet.getRow(j);// 循环获取行数
				if (row != null) {
					short cells = row.getLastCellNum();
					obj[j] = new Object[cells];
					for (int k = 0; k < cells; k++) {
						Cell cell = row.getCell(k);
						String text = "";
						if (cell != null) {
							switch (cell.getCellType()) {
							// 字符转类型
							case Cell.CELL_TYPE_STRING:
								text = cell.getRichStringCellValue().toString();
								break;
							// 数值类型
							case Cell.CELL_TYPE_NUMERIC:
								if (DateUtil.isCellDateFormatted(cell)){
									
									text = sdf.format(cell.getDateCellValue());
								}	
								else{
									Double d = new Double(cell
											.getNumericCellValue());
									text = d.longValue()+"";
								}	
								break;
							// 公式类型
							case Cell.CELL_TYPE_FORMULA:
								text = String.valueOf(cell
										.getNumericCellValue());
								if (text.equals("NaN"))
									text = cell.getRichStringCellValue()
											.toString();
								break;
							// 布尔类型
							case Cell.CELL_TYPE_BOOLEAN:
								text = String.valueOf(cell
										.getBooleanCellValue());
								break;
							// 空值类型
							case Cell.CELL_TYPE_BLANK:
								text = "";
								break;
							// 报错类型
							case Cell.CELL_TYPE_ERROR:
								text = "";
								break;
							default:
								text = cell.getStringCellValue();
								break;
							}
						} else {
							text = "";
						}
						obj[j][k] = text;
					}
				}
			}
		}
		return obj;
	}
}
